﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using TracNghiem.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;

namespace TracNghiem.Areas.Admin.Models
{
    public class PhanLoaiModel
    {
        public string id { get; set; }
        [Required(ErrorMessage = "Tên phân loại không được để trống!")]
        public string  name{ get; set; }        
        public string dichvu_id { get; set; }
        public string vi_tri { get; set; }
        public string ten_dich_vu { get; set; }
        public SelectList service_id { get; set; }
        public List<PhanLoaiModel> getPhan_Loai()
        {
            List<PhanLoaiModel> PhanLoai = new List<PhanLoaiModel>();
            OracleDataReader odr = General.Lib_GetDataReader("select a.id,a.name,b.id dichvu_id,a.vi_tri,b.name ten_dich_vu from phanloai a,dichvu b where a.dichvu_id=b.id order by a.vi_tri");            
            while (odr.Read())
            {                
                PhanLoai.Add(new PhanLoaiModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),                    
                    dichvu_id = odr["dichvu_id"].ToString(),
                    vi_tri = odr["vi_tri"].ToString(),
                    ten_dich_vu = odr["ten_dich_vu"].ToString()
                });
            }
            General.conn_global.Close();
            return PhanLoai;
        }

        public List<PhanLoaiModel> getPhan_LoaiByDV(int id)
        {
            List<PhanLoaiModel> PhanLoai = new List<PhanLoaiModel>();
            OracleDataReader odr = General.Lib_GetDataReader("select a.id,a.name,b.id dichvu_id,a.vi_tri,b.name ten_dich_vu from phanloai a,dichvu b where (a.dichvu_id=b.id or a.dichvu_id=b.parent_id) and b.id=" + id + " order by a.vi_tri");
            while (odr.Read())
            {
                PhanLoai.Add(new PhanLoaiModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    dichvu_id = odr["dichvu_id"].ToString(),
                    vi_tri = odr["vi_tri"].ToString(),
                    ten_dich_vu = odr["ten_dich_vu"].ToString()
                });
            }
            General.conn_global.Close();
            return PhanLoai;
        }

        //public List<PhanLoaiModel> getPhanLoaiById(int id)
        //{
        //    List<PhanLoaiModel> PhanLoai = new List<PhanLoaiModel>();
        //    OracleDataReader odr = General.Lib_GetDataReader("SELECT a.*,b.name ten_dich_vu FROM chu_de a,dichvu b where a.dichvu_id=b.id and a.id="+id+" order by b.id,a.ngay_ket_thuc desc,a.name");
        //    int ngay_taoIndex = 0;
        //    int ngay_ket_thucIndex = 0;
        //    while (odr.Read())
        //    {
        //        ngay_taoIndex = odr.GetOrdinal("ngay_tao");
        //        ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
        //        PhanLoai.Add(new PhanLoaiModel
        //        {
        //            id = odr["id"].ToString(),
        //            name = odr["name"].ToString(),
        //            thoi_gian = odr["thoi_gian"].ToString(),
        //            ghi_chu = odr["ghi_chu"].ToString(),
        //            so_cau = odr["so_cau"].ToString(),
        //            status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
        //            ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
        //            ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
        //            dichvu_id = odr["dichvu_id"].ToString(),
        //            ten_dich_vu = odr["ten_dich_vu"].ToString()
        //        });
        //    }
        //    return PhanLoai;
        //}

        public void addPhanLoai(PhanLoaiModel ts)
        {            
            string sql = "INSERT INTO phanloai";
            sql += " select nvl((select max(id)+1 from phanloai),1),'" + ts.name + "'," + ts.dichvu_id + ",nvl((select max(vi_tri)+1 from phanloai where dichvu_id="+ts.dichvu_id+"),1) from dual";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void updatePhanLoai(PhanLoaiModel ts)
        {

            string sql = "UPDATE phanloai SET name='" + ts.name + "',  where id='" + ts.id + "'";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void updateVi_tri(List<PhanLoaiModel> ts)
        {
            for (int i = 0; i < ts.ToArray().Length;i++ )
            {
                string sql = "UPDATE phanloai SET vi_tri='" + ts[i].vi_tri + "'  where id='" + ts[i].id + "'";
                General.Lib_ExecuteNonQuery(sql);

            }
            General.conn_global.Close();
        }
        public void deletePhanLoai(int id)
        {
            string sql = "DELETE FROM phanloai WHERE id=" + id + "";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public int checkExist(PhanLoaiModel ts)
        {
            string sql = "SELECT * FROM phanloai WHERE name='" + ts.name + "'";
            return General.Lib_CheckExist(sql);
            General.conn_global.Close();
        }        

        public List<PhanLoaiModel> FindPhanLoai(string str)
        {
            List<PhanLoaiModel> PhanLoai = new List<PhanLoaiModel>();
            string sql = "select a.id,a.name,b.id dichvu_id,a.vi_tri,b.name ten_dich_vu from phanloai a,dichvu b where a.dichvu_id=b.id " + str + " order by a.vi_tri";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                PhanLoai.Add(new PhanLoaiModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    dichvu_id = odr["dichvu_id"].ToString(),
                    vi_tri = odr["vi_tri"].ToString(),
                    ten_dich_vu = odr["ten_dich_vu"].ToString()
                });
            }
            General.conn_global.Close();
            return PhanLoai;
        }
        
    }
}